#!/usr/bin/env python
#
#       persist.py
#
#       Copyright 2009 Chris Glass <tribaal@gmail.com>
#
#       This program is free software: you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation, either version 3 of the License, or
#       (at your option) any later version.
#
#       This program is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.
#
#       You should have received a copy of the GNU General Public License
#       along with this program.  If not, see <http://www.gnu.org/licenses/>.

import datetime
import MySQLdb
import cPickle as pickle

from glassvine.properties import props
from glassvine.persistance import database_accessor

class database_accessor_implementation(database_accessor):
    '''This is the main database access object for MySQL. 
    It holds all the CRUD
    (Create Read Update Delete) accessors, to hode SQL from the rest of
    the program'''

    def __init__(self, config):
        
        # Load the specific configuraiton for this plugin
        specific_props = props.load_properties('glassvine/plugins/mysql.conf')
        # Now merge it with the general configuration.
        # The specific configuration overrides the general one.
        for key, value in specific_props.iteritems():
            config[key] = value

        self.conn = MySQLdb.connect(host=config['mysql_Host'], user=config['mysql_User'] , passwd=config['mysql_Passwd'], db=config['mysql_Db'])

        self.create_database()

    def create_database(self):
        '''This creates the database tables if they don't already exist.'''

        c = self.conn.cursor()
        # Create table
        c.execute('''begin''')

        c.execute('''create table if not exists cache
            (url text, type text , insert_date timestamp, data text)''')

        c.execute('''create table if not exists header
            (url text, name text, value text)''')
            
        # crate an index on the header table (there are potentally lots
        # of entries to retreive by URL...)
        c.execute('''create index if not exists idx_header on 
            header (url)''')

        # Save (commit) the changes
        c.execute("commit")

    def insert_cache(self, url, mime_type , date, data, headers=None):
        '''Inserts a cache entry in the database'''
        #print "Inserting:"
        #print "url: ", url
        #print "type: ", type
        #print "date: ", date
        #print "data exists: ", data != None
        #print ""
        c = self.conn.cursor()
        c.execute('''begin''')
        c.execute('''insert into cache
          values (?, ?, ?, ?)''', (url, mime_type, date.isoformat(' '), pickle.dumps(str(data)),))
        if headers:
            for k, v in headers.iteritems():
                c.execute('''insert into header values(?,?,?)''', (url, k, v,))
        # Commit the transaction.
        c.execute("commit")

    def retrieve_entry(self, url):
        '''Returns a row of data from the database as a dictionary'''
        c = self.conn.cursor()
        c.execute('''begin''')
        c.execute('''select * from cache where url==?''', (url,))
        
        content = list(c)
        
        c.execute('''select name, value from header where (url==?)''', (url,))
        
        headers = list(c)
        
        c.execute("commit")

        if len(content) > 0:
            parsed = self._parse_return(content.pop())
            if len(headers) > 0:
                parsed_headers = self._parse_headers(headers)
                parsed['headers'] = parsed_headers
            return parsed
        else:
            return None

    def print_table(self) :
        '''Displays the content of the database (this is only for tests
        and/or debugging purposes)'''
        c = self.conn.cursor()
        c.execute('''begin''')
        c.execute('''select * from cache''')
        output = list()
        c.execute("commit")
        #print("Table:")
        for row in c :
            output.append(str(row))
        print output
        
    def get_insert_date_for_url(self, url):
        c = self.conn.cursor()
        c.execute('''begin''')
        c.execute('''select insert_date from cache where url=?''', (url,))
        insert_date = list(c)
        c.execute('''commit''')
        if len(insert_date) == 0:
            return None
        else:
            insert_date = insert_date.pop()
        return self._datetime_from_iso_format(insert_date[0])

    def _parse_return(self, row):
        '''This helper method simply converts a raw row from the database
        to a more convenient dict[]'''
        return_dict = {}
        return_dict['url'] = row[0].encode('utf-8')
        return_dict['type'] = row[1].encode('utf-8')
        return_dict['date_fetched'] = self._datetime_from_iso_format(row[2].encode('utf-8'))
        return_dict['content'] = pickle.loads(str(row[3]))
        return return_dict
        
    def _parse_headers(self, db_headers):
        '''Make the returned row (list) from the database in a nice 
        dictionnary.'''
        headers = {}
        for header in db_headers:
            headers[header[0]] = header[1]
        return headers
        

    def _datetime_from_iso_format(self, iso_string):
        '''While the syntax looks ugly, it's quite fast.
        In Python, splices (using the [:] operator) are much more
        efficient than standard String manipulation function like split()
        and join()'''
        year = int(iso_string[:4])
        iso_string = iso_string[5:] #Includes the separator
        month = int(iso_string[:2])
        iso_string = iso_string[3:]
        day = int(iso_string[:2])
        iso_string = iso_string[3:]
        hour = int(iso_string[:2])
        iso_string = iso_string[3:]
        minute = int(iso_string[:2])
        iso_string = iso_string[3:]
        second = int(iso_string[:2])
        iso_string = iso_string[3:]
        miliseconds = int(iso_string)
        date = datetime.datetime(year, month, day, hour, minute, second, miliseconds)
        return date








